-- 加油到支付时长的脚本
insert overwrite table mid_tpc
SELECT
t.Transaction_ID,
t.station_id,
t.transaction_number,
p.pos_number,
c.cashier_number,
c.cashier_name,
t.transaction_db_date
FROM
mid.transactions t
JOIN pos_terminal p ON t.pos_id = p.pos_id and p.station_id=t.station_id
JOIN cashiers c ON c.cashier_id = t.cashier_id and t.station_id=c.station_id;

insert overwrite table  mid_serve_time
SELECT
tpc.Transaction_ID,
tpc.station_id,
tpc.transaction_number,
tpc.pos_number,
tpc.cashier_number,
tpc.cashier_name,
ti.item_number,
g.Grade_Name,
hd.Delivery_Volume,
hd.Delivery_Value,
hd.Completed_TS ,
tpc.transaction_db_date ,
unix_timestamp(tpc.transaction_db_date)-unix_timestamp(hd.Completed_TS) seconds
from mid_tpc as tpc
JOIN mid.transaction_items_oil ti ON tpc.transaction_id = ti.transaction_id and ti.station_id=tpc.station_id
AND ti.item_type = 2
JOIN Hose_Delivery hd ON ti.item_id = hd.delivery_id and tpc.station_id=hd.station_id
JOIN Hoses h ON hd.hose_id = h.hose_id and tpc.station_id=h.station_id
JOIN Grades g ON h.grade_id = g.grade_id and g.station_id=tpc.station_id
where unix_timestamp(tpc.transaction_db_date)-unix_timestamp(ti.transaction_date)>-24*3600
and unix_timestamp(tpc.transaction_db_date)-unix_timestamp(ti.transaction_date)<24*3600;

insert overwrite table  res_serve_time
select substr(transaction_db_date,1,16),station_id,sum(seconds),count(seconds),max(seconds),min(seconds),sum(seconds)/count(seconds)
from mid_serve_time where seconds>=0
group by station_id,substr(transaction_db_date,1,16);